﻿using System.Collections.Generic;
using System.Linq;
using System.Transactions;
using iMobileS.Models.Partial;

namespace iMobileS.Models
{
    internal interface ICategoriesRepository
    {
        List<Categories> GetCategories();
        List<Products> GetProductsByCategory(int cateId);
        List<Regions> GetRegions();
        bool InsertOrder(Orders orders, List<OrderDetails> orderDetails);
        List<Employees> GetEmployees();
        List<Shippers> GetShippers();
        List<Customers> GetCustomers();
    }
    public class CategoriesRepository : RepositoryBase, ICategoriesRepository
    {
        public CategoriesRepository(IDatabase db, ICache cacher)
            : base(db, cacher)
        { }

        public List<Categories> GetCategories()
        {
            return DB.GetManyRecords<Categories>("spCategoriesSelectInfo").ToList();
        }

        public List<Products> GetProductsByCategory(int cateId)
        {
            List<List<object>> param = new List<List<object>>();
            param.Add(new List<object>() { cateId });
            return DB.GetManyRecordsWithPrams<Products>("spProductSelectByCategory", param).ToList();
        }

        public List<Regions> GetRegions()
        {
            return DB.GetManyRecords<Regions>("spRegionSelect").ToList();
        }

        public bool InsertOrder(Orders orders, List<OrderDetails> orderDetails)
        {
            var iSuccess = false;
            var affectedOrderDetail = 0;
            var affectedOrder = 0;
            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
            {
                affectedOrder = DB.GetSingleRecord<int>("spOrdersInsert", orders.CustomerID,
                                orders.EmployeeID,
                                orders.ShipVia,
                                orders.Freight,
                                orders.ShipName,
                                orders.ShipAddress,
                                orders.ShipCity,
                                orders.ShipRegion,
                                orders.ShipPostalCode,
                                orders.ShipCountry,
                                orders.RequiredDate);
                if (affectedOrder > 0)
                {
                    List<List<object>> param =
                   (orderDetails.Select(
                       s =>
                       new List<object>()
                           {
                               affectedOrder,s.ProductID,s.UnitPrice,s.Quantity,s.Discount
                           })).ToList();
                    affectedOrderDetail = DB.ExecuteNoneBatchQuery("spOrderDetailsInsert", param);
                    if (affectedOrderDetail > 0)
                    {
                        scope.Complete();
                        iSuccess = true;
                    }
                }

            }
            return iSuccess;
        }

        public List<Employees> GetEmployees()
        {
            return DB.GetManyRecords<Employees>("spEmployeesSelect").ToList();
        }

        public List<Shippers> GetShippers()
        {
            return DB.GetManyRecords<Shippers>("spShippersSelect").ToList();
        }

        public List<Customers> GetCustomers()
        {
            return DB.GetManyRecords<Customers>("spCustomersSelect").ToList();
        }
    }
}